﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;

namespace DataControl
{
    public class DataAccess
    {
        DbTransactionScope ts;
        public int GetCount(string sql, params object[] parameters)
        {
            Database db = new Database();
            DbCommand cmd = db.GetComamnd();
            cmd.CommandText = sql;
            if (parameters != null && parameters.Length != 0)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    DbParameter parameter = cmd.CreateParameter();
                    if (parameters[i] == null)
                    {
                        parameter.Value = System.DBNull.Value;
                    }
                    else
                    {
                        parameter.Value = parameters[i];
                    }

                    cmd.Parameters.Add(parameter);
                }
            }
            if (cmd.Connection.State == ConnectionState.Executing)
            {
                throw new Exception("系统正在操作数据库,无法同时启动多个查询或更新");
            }
            if (cmd.Connection.State != ConnectionState.Open)
            {
                try
                {
                    cmd.Connection.Open();
                }
                catch
                {
                    throw new Exception("数据库连接无法打开,请检查数据库连接是否正确");
                }
            }
            object Scalar = cmd.ExecuteScalar();
            cmd.Connection.Close();
            cmd.Connection = null;
            return Convert.ToInt32(Scalar);
        }

        public int ExcuteNoQuery(string sql, params object[] parameters)
        {
            Database db = new Database();
            DbCommand cmd = db.GetComamnd();
            cmd.CommandText = sql;
            if (parameters != null && parameters.Length != 0)
            {
                cmd.Parameters.AddRange(parameters);
                //for (int i = 0; i < parameters.Length; i++)
                //{
                //    DbParameter parameter = cmd.CreateParameter();
                //    if (parameters[i] == null)
                //    {
                //        parameter.Value = System.DBNull.Value;
                //    }
                //    else
                //    {
                //        parameter.Value = parameters[i];
                //    }
                //    cmd.Parameters.Add(parameter);
                //}
            }
            if (cmd.Connection.State == ConnectionState.Executing)
            {
                throw new Exception("系统正在操作数据库,无法同时启动多个查询或更新");
            }
            if (cmd.Connection.State != ConnectionState.Open)
            {
                try
                {
                    cmd.Connection.Open();
                }
                catch
                {
                    throw new Exception("数据库连接无法打开,请检查数据库连接是否正确");
                }
            }

            int isok = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            cmd.Connection = null;
            return isok;
        }

        public int ExcuteNoQuery(string sql, bool flag, params object[] parameters)
        {
            Database db = new Database();
            DbCommand cmd = db.GetComamnd();
            cmd.CommandText = sql;
            if (parameters != null && parameters.Length != 0)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    DbParameter parameter = cmd.CreateParameter();
                    if (parameters[i] == null)
                    {
                        parameter.Value = System.DBNull.Value;
                    }
                    else
                    {
                        parameter.Value = parameters[i];
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
            if (cmd.Connection.State == ConnectionState.Executing)
            {
                throw new Exception("系统正在操作数据库,无法同时启动多个查询或更新");
            }
            if (cmd.Connection.State != ConnectionState.Open)
            {
                try
                {
                    cmd.Connection.Open();
                }
                catch
                {
                    throw new Exception("数据库连接无法打开,请检查数据库连接是否正确");
                }
            }

            int isok = cmd.ExecuteNonQuery();
            return isok;
        }

        public object ExecuteScalar(string sql, params object[] parameters)
        {
            Database db = new Database();
            DbCommand cmd = db.GetComamnd();
            cmd.CommandText = sql;
            if (parameters != null && parameters.Length != 0)
            {
                cmd.Parameters.AddRange(parameters);
                //for (int i = 0; i < parameters.Length; i++)
                //{
                //    DbParameter parameter = cmd.CreateParameter();
                //    if (parameters[i] == null)
                //    {
                //        parameter.Value = System.DBNull.Value;
                //    }
                //    else
                //    {
                //        parameter.Value = parameters[i];
                //    }
                //    cmd.Parameters.Add(parameter);
                //}
            }
            if (cmd.Connection.State == ConnectionState.Executing)
            {
                throw new Exception("系统正在操作数据库,无法同时启动多个查询或更新");
            }
            if (cmd.Connection.State != ConnectionState.Open)
            {
                try
                {
                    cmd.Connection.Open();
                }
                catch
                {
                    throw new Exception("数据库连接无法打开,请检查数据库连接是否正确");
                }
            }
            object o = cmd.ExecuteScalar();
            cmd.Connection.Close();
            cmd.Connection = null;
            return o;
        }

        public DataTable ExecuteDataTable(string sql, params object[] parameters)
        {
            Database db = new Database();
            DbCommand cmd = db.GetComamnd();
            cmd.CommandText = sql;
            if (parameters != null && parameters.Length != 0)
            {
                cmd.Parameters.AddRange(parameters);
                //for (int i = 0; i < parameters.Length; i++)
                //{
                //    DbParameter parameter = cmd.CreateParameter();
                //    if (parameters[i] == null)
                //    {
                //        parameter.Value = System.DBNull.Value;
                //    }
                //    else
                //    {
                //        parameter.Value = parameters[i];
                //    }
                //    cmd.Parameters.Add(parameter);
                //}
            }
            if (cmd.Connection.State == ConnectionState.Executing)
            {
                throw new Exception("系统正在操作数据库,无法同时启动多个查询或更新");
            }
            DbDataAdapter ada = db.GetAdapter(cmd);
            DataTable dt = new DataTable();
            ada.Fill(dt);
            return dt;
        }

        #region 执行ORACLE分页查询
        public DataTable ExecuteDataTableForPager(string sql, int page, int pagesize, params object[] parameters)
        {
            Database db = new Database();
            DbCommand cmd = db.GetComamnd();
            string sqlpager = "";
            if (db.ProviderName.Contains("OracleClient"))
            {
                sqlpager = string.Format(" select * from (select rownum as rid, result.* from ({0}) result where rownum <= {1}*{2}) where rid > ({3}-1)*{4} ", sql, page, pagesize, page, pagesize);
            }
            cmd.CommandText = sqlpager;
            if (parameters != null && parameters.Length != 0)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    DbParameter parameter = cmd.CreateParameter();
                    if (parameters[i] == null)
                    {
                        parameter.Value = System.DBNull.Value;
                    }
                    else
                    {
                        parameter.Value = parameters[i];
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
            if (cmd.Connection.State == ConnectionState.Executing)
            {
                throw new Exception("系统正在操作数据库,无法同时启动多个查询或更新");
            }
            DbDataAdapter ada = db.GetAdapter(cmd);
            DataTable dt = new DataTable();
            ada.Fill(dt);
            return dt;
        }
        #endregion

        public string ExecuteProcedure(string procName, params object[] parameters)
        {
            Database db = new Database();
            DbCommand cmd = db.GetComamnd();
            if (cmd.Parameters != null) cmd.Parameters.Clear();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            for (int i = 0; i < parameters.Length; i++)
            {
                DbParameter parameter = cmd.CreateParameter();
                if (parameters[i] == null)
                {
                    parameter.Value = System.DBNull.Value;
                }
                else
                {
                    parameter.Value = parameters[i];
                }
                cmd.Parameters.Add(parameter);
            }
            if (cmd.Connection.State != ConnectionState.Open)
            {
                try
                {
                    cmd.Connection.Open();
                }
                catch
                {
                    throw new Exception("数据库连接无法打开,请检查数据库连接是否正确");
                }
            }

            string value = cmd.ExecuteNonQuery().ToString();
            cmd.Connection.Close();
            cmd.Connection = null;
            return value;
        }

        /// <summary>
        /// 使用传入的数据库连接信息查询数据
        /// </summary>
        /// <param name="dbi"></param>
        /// <param name="strSPName"></param>
        /// <param name="dictParameter"></param>
        /// <param name="dcError"></param>
        /// <returns></returns>
        public DataTable GetDataTableBySPName(string strSPName, Dictionary<string, object> dictParameter)
        {
            DC_Error dcError = new DC_Error();
            SqlConnection sqlConn = null;
            DataTable dt = new DataTable();
            try
            {
                sqlConn = new SqlConnection(GetSQLConnection());

                int Rtn = SQLServer.ExecuteDataTableQueryProcedure("dbo", strSPName, ref dictParameter, sqlConn, sqlConn, ref dt);
                if (Rtn != 0)
                {
                    dcError.ErrorCode = "-1";
                    dcError.ErrorDesc = string.Format("SP {0} fail", strSPName);
                }
            }
            catch (Exception ex)
            {
                dcError.ErrorCode = "-1";
                dcError.ErrorDesc = ex.Message;
            }
            finally
            {
                sqlConn = null;
            }

            return dt;
        }

        public DataSet GetDataSetBySPName(string strSPName, Dictionary<string, object> dictParameter)
        {
            DC_Error dcError = new DC_Error();
            SqlConnection sqlConn = null;
            DataSet ds = new DataSet();
            try
            {
                sqlConn = new SqlConnection(GetSQLConnection());

                int Rtn = SQLServer.ExecuteDataSetQueryProcedure("dbo", strSPName, ref dictParameter, sqlConn, sqlConn, ref ds);
                if (Rtn != 0)
                {
                    dcError.ErrorCode = "-1";
                    dcError.ErrorDesc = string.Format("SP {0} fail", strSPName);
                }
            }
            catch (Exception ex)
            {
                dcError.ErrorCode = "-1";
                dcError.ErrorDesc = ex.Message;
            }
            finally
            {
                sqlConn = null;
            }

            return ds;
        }

        /// <summary>
        /// 执行多条语句 sch 2012-10-08
        /// </summary>
        /// <param name="SqlList">集合</param>
        /// <returns></returns>
        public bool ExecuteArrayList(System.Collections.ArrayList SqlList)
        {
            Database db = new Database();
            DbCommand cmd = db.GetComamnd();
            if (cmd.Connection.State == ConnectionState.Executing)
            {
                throw new Exception("系统正在操作数据库,无法同时启动多个查询或更新");
            }
            if (cmd.Connection.State != ConnectionState.Open)
            {
                try
                {
                    cmd.Connection.Open();
                }
                catch
                {
                    throw new Exception("数据库连接无法打开,请检查数据库连接是否正确");
                }
            }

            bool iserror = false;
            string strerror = "";
            DbTransaction SqlTran = cmd.Connection.BeginTransaction();
            try
            {
                for (int i = 0; i < SqlList.Count; i++)
                {

                    DbCommand _command = cmd;
                    _command.Connection = cmd.Connection;
                    _command.CommandText = SqlList[i].ToString();
                    _command.Transaction = SqlTran;
                    _command.ExecuteNonQuery();
                }

            }
            catch (Exception ex)
            {
                iserror = true;
                strerror = ex.Message;

            }
            finally
            {

                if (iserror)
                {
                    SqlTran.Rollback();
                    throw new Exception(strerror);
                }
                else
                {
                    SqlTran.Commit();
                }
                cmd.Connection.Close();
                cmd.Connection = null;
            }
            if (iserror)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public DataTable PageSize(string sql, int page, int pagesize)
        {
            DataTable dt = ExecuteDataTableForPager(sql, page, pagesize, null);
            if (dt != null && dt.Rows.Count > 0)
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    dt.Columns.Add("Totalpagesize");
                    dt.Rows[0]["Totalpagesize"] = GetCount("select count(*) from ( " + sql.ToString() + " )", null);
                }
            }
            return dt;
        }

        #region 执行查询(SqlServer)，并以DataTable返回分页后结果集 PageSize(string sql, int page, int pagesize, string paramid)
        /// <summary>
        /// 分页,注意，如果带orderby则需要在前面加top
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="page">当前页</param>
        /// <param name="pagezise">每页显示条数</param>
        /// <param name="paramid">主键列</param>
        /// <returns></returns>
        public DataTable PageSize(string sql, int page, int pagesize, string paramid)
        {
            if (paramid == "")
            {
                return null;
            }
            else
            {
                System.Text.StringBuilder strSql = new StringBuilder();
                strSql.Append(" create table #temp");
                strSql.Append(" (");
                strSql.Append(" Pager2011_ID INT IDENTITY(1, 1) PRIMARY KEY , ");
                strSql.Append(" tableid nvarchar(1000)");
                strSql.Append(" ); \r\n");
                strSql.Append(" Insert Into #temp(tableid)");
                strSql.Append(" Select ");
                strSql.Append(paramid);
                strSql.Append(" From ");
                strSql.AppendFormat(" ( {0} ) mm_201011 ;\r\n ", sql);
                strSql.AppendFormat(" select t.*,b.Pager2011_ID from ( {0} ) t inner join #temp b on t.{5}=b.tableid where Pager2011_ID <= {1}*{2} and Pager2011_ID > ({3}-1)*{4}", sql, page, pagesize, page, pagesize, paramid);
                DataTable dt = ExecuteDataTable(strSql.ToString(), null);
                if (dt != null && dt.Rows.Count > 0)
                {
                    dt.Columns.Add("Totalpagesize");
                    dt.Rows[0]["Totalpagesize"] = ExecuteScalar("select count(*) from (" + sql.ToString() + ") ttt");
                }
                return dt;

            }
        }

        /// <summary>
        /// 分页,注意，如果带orderby则需要在前面加top
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="page">当前页</param>
        /// <param name="pagezise">每页显示条数</param>
        /// <param name="paramid">主键列</param>
        /// <param name="orderby">排序方式 ，例如 create_time desc,字段需要在查询结果中出现</param>
        /// <returns></returns>
        public DataTable PageSize(string sql, int page, int pagesize, string paramid, string orderby)
        {
            if (paramid == "")
            {
                return null;
            }
            else
            {
                System.Text.StringBuilder strSql = new StringBuilder();
                strSql.Append(" create table #temp");
                strSql.Append(" (");
                strSql.Append(" Pager2011_ID INT IDENTITY(1, 1) PRIMARY KEY , ");
                strSql.Append(" tableid nvarchar(1000)");
                strSql.Append(" ); \r\n");
                strSql.Append(" Insert Into #temp(tableid)");
                strSql.Append(" Select ");
                strSql.Append(paramid);
                strSql.Append(" From ");
                strSql.AppendFormat(" ( {0} ) mm_201011 order by mm_201011.{1} ;\r\n ", sql, orderby);
                strSql.AppendFormat(" select t.*,b.Pager2011_ID from ( {0} ) t inner join #temp b on t.{5}=b.tableid where Pager2011_ID <= {1}*{2} and Pager2011_ID > ({3}-1)*{4} order by t.{6}", sql, page, pagesize, page, pagesize, paramid, orderby);
                DataTable dt = ExecuteDataTable(strSql.ToString(), null);
                if (dt != null && dt.Rows.Count > 0)
                {
                    dt.Columns.Add("Totalpagesize");
                    dt.Rows[0]["Totalpagesize"] = ExecuteScalar("select count(*) from (" + sql.ToString() + ") ttt");
                }
                return dt;

            }
        }
        #endregion
        /// <summary>
        /// 分页查询（注意：如果带orderby则sql组织时需带top）
        /// qiucm 2013-11-01
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="page">当前页</param>
        /// <param name="pagesize">每页显示条数</param>
        /// <param name="paramid">主键列（必须出现在sql查询结果中）</param>
        /// <returns></returns>
        public DataTable PageSize2(string sql, int page, int pagesize, string paramid)
        {
            int lastPageSize = 0;
            lastPageSize = page <= 0 ? 0 : (page - 1) * pagesize;

            if (paramid == "")
            {
                return null;
            }
            else
            {
                System.Text.StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("select top {2} * from ({0}) t1 where {3} not in (select top {1} {3} from ({0}) t2)",
                    sql, lastPageSize, pagesize, paramid);

                DataTable dt = ExecuteDataTable(strSql.ToString(), null);
                if (dt != null && dt.Rows.Count > 0)
                {
                    dt.Columns.Add("Totalpagesize");
                    dt.Rows[0]["Totalpagesize"] = ExecuteScalar("select count(*) from (" + sql + ") ttt");
                }
                return dt;

            }
        }

        #region hxb 增加方法

        /// <summary>
        /// 打开数据库连接，boo为true则打开事务
        /// </summary>
        /// <param name="boo"></param>
        public void OpenTrans()
        {
            ts = new DbTransactionScope();
        }

        #region 增加记录(批量增加与单个增加)
        /// <summary>
        /// 增加多条记录
        /// </summary>
        /// <param name="dtParam">字段</param>
        /// <param name="strTableName">表名</param>
        public void Insert(DataTable dtParam, string strTableName)
        {
            for (int i = 0; i < dtParam.Rows.Count; i++)
            {
                Hashtable ht = new Hashtable();

                for (int j = 0; j < dtParam.Columns.Count; j++)
                {
                    ht[dtParam.Columns[j].ColumnName.ToString()] = dtParam.Rows[i][j];
                }

                Insert(ht, strTableName);
            }
        }

        public string Insert(Hashtable htParam, string strTableName)
        {
            string strCount = string.Empty;
            string strSQL = string.Empty;
            string strSqlKey = string.Empty;
            string strSqlValue = string.Empty;

            Database db = new Database();
            DbCommand DBComm = db.GetComamnd();

            foreach (string key in htParam.Keys)
            {
                ConvertString(ref strSqlKey, ",", key);
                ConvertString(ref strSqlValue, ",", "@" + key);
            }

            SetParameters(DBComm, htParam, null, strTableName);

            strSQL = "INSERT INTO " + strTableName + " (" + strSqlKey + ") VALUES (" + strSqlValue + "); select @@identity ";

            DBComm.CommandText = strSQL;

            try
            {
                if (DBComm.Connection.State != ConnectionState.Open)
                {
                    DBComm.Connection.Open();
                }

                strCount = DBComm.ExecuteScalar().ToString();

                //没有打开事务的情况下关闭连接
                if (ts == null)
                {
                    DBComm.Connection.Close();
                    DBComm.Connection = null;
                }

            }
            catch
            {
                if (ts != null)
                {
                    RollBackConnection();
                }
                else
                {
                    DBComm.Connection.Close();
                    DBComm.Connection = null;
                }

                throw new Exception(strSQL);
            }

            return strCount;
        }

        #endregion

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="htParam">更新字段集合</param>
        /// <param name="htWhere">条件集合</param>
        /// <param name="strTableName">表名</param>
        public void Update(Hashtable htParam, Hashtable htWhere, string strTableName)
        {
            int intCount = 0;
            string strSQL = "";
            string strSqlStmt = "";
            string strWhere = "";

            Database db = new Database();
            DbCommand DBComm = db.GetComamnd();

            foreach (string key in htParam.Keys)
            {
                ConvertString(ref strSqlStmt, ",", key + "=@" + key);
            }

            foreach (string key in htWhere.Keys)
            {
                ConvertString(ref strWhere, " AND ", key + "=@" + key);
            }

            SetParameters(DBComm, htParam, htWhere, strTableName);

            strSQL = "UPDATE " + strTableName + " SET " + strSqlStmt + " WHERE " + strWhere;

            DBComm.CommandText = strSQL;

            try
            {
                if (DBComm.Connection.State != ConnectionState.Open)
                {
                    DBComm.Connection.Open();
                }

                intCount = DBComm.ExecuteNonQuery();

                //没有打开事务的情况下关闭连接
                if (ts == null)
                {
                    DBComm.Connection.Close();
                    DBComm.Connection = null;
                }
            }
            catch
            {
                if (ts != null)
                {
                    RollBackConnection();
                }
                else
                {
                    DBComm.Connection.Close();
                    DBComm.Connection = null;
                }

                throw new Exception(strSQL);
            }
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="htWhere">删除条件</param>
        public void Delete(Hashtable htWhere, string strTableName)
        {
            string strSQL = "";
            string strWhere = "";

            Database db = new Database();
            DbCommand DBComm = db.GetComamnd();

            foreach (string key in htWhere.Keys)
            {
                if (strWhere != "")
                {
                    strWhere += " AND ";
                }

                strWhere += key + "='" + htWhere[key].ToString() + "'";
            }

            strSQL = " DELETE " + strTableName + " WHERE " + strWhere;
            DBComm.CommandText = strSQL;

            try
            {
                if (DBComm.Connection.State != ConnectionState.Open)
                {
                    DBComm.Connection.Open();
                }

                int intCount = DBComm.ExecuteNonQuery();

                //没有打开事务的情况下关闭连接
                if (ts == null)
                {
                    DBComm.Connection.Close();
                    DBComm.Connection = null;
                }
            }
            catch
            {
                if (ts != null)
                {
                    RollBackConnection();
                }
                else
                {
                    DBComm.Connection.Close();
                    DBComm.Connection = null;
                }

                throw new Exception(strSQL);
            }

        }

        /// <summary>
        /// 拼字符串
        /// </summary>
        /// <param name="strWhere"></param>
        /// <param name="strMark"></param>
        /// <param name="strValue"></param>
        private void ConvertString(ref string strWhere, string strMark, string strValue)
        {
            if (strWhere != null && !strWhere.Equals(""))
            {
                strWhere += strMark;
            }
            strWhere += strValue;
        }

        /// <summary>
        /// 设置参数sqlserver
        /// </summary>
        /// <param name="cmd">数据命令</param>
        /// <param name="hashParam">参数集合</param>
        /// <param name="htWhere">where后的参数集合，Update()用到</param>
        /// <param name="strTableName">表名</param>
        private void SetParameters(DbCommand cmd, Hashtable hashParam, Hashtable htWhere, string strTableName)
        {
            DbParameterCollection parameters = cmd.Parameters;

            string strValue;
            DbType type;
            bool booString;
            Hashtable htColumns = GetColumnInfo(strTableName);

            if (hashParam != null)
            {
                foreach (string key in hashParam.Keys)
                {
                    strValue = hashParam[key].ToString();
                    type = GetDbType(key, htColumns);
                    booString = (type.Equals(SqlDbType.VarChar) || type.Equals(SqlDbType.Char));

                    DbParameter dp = cmd.CreateParameter();
                    dp.ParameterName = "@" + key;
                    dp.DbType = type;

                    parameters.Add(dp);

                    if (strValue == null || (strValue.Equals("") && !booString))
                    {
                        parameters["@" + key].Value = DBNull.Value;
                    }
                    else
                    {
                        parameters["@" + key].Value = strValue;
                    }
                }
            }

            //where后的参数
            if (htWhere != null)
            {
                foreach (string key in htWhere.Keys)
                {
                    strValue = htWhere[key].ToString();
                    type = GetDbType(key, htColumns);
                    booString = (type.Equals(SqlDbType.VarChar) || type.Equals(SqlDbType.Char));

                    DbParameter dp = cmd.CreateParameter();
                    dp.ParameterName = "@" + key;
                    dp.DbType = type;

                    parameters.Add(dp);

                    if (strValue == null || (strValue.Equals("") && !booString))
                    {
                        parameters["@" + key].Value = DBNull.Value;
                    }
                    else
                    {
                        parameters["@" + key].Value = strValue;
                    }
                }
            }
        }

        /// <summary>
        /// 得到列类型sqlserver
        /// </summary>
        /// <param name="strKey"></param>
        /// <param name="hashColumns">数据表列集合</param>
        /// <returns></returns>
        private DbType GetDbType(string strKey, Hashtable hashColumns)
        {
            DbType dbType;

            switch (Type.GetTypeCode((Type)hashColumns[strKey]))
            {
                case TypeCode.Int16: dbType = DbType.Int16; break;
                case TypeCode.Int32: dbType = DbType.Int32; break;
                case TypeCode.Int64: dbType = DbType.Int64; break;
                case TypeCode.Decimal: dbType = DbType.Decimal; break;
                case TypeCode.Double: dbType = DbType.Double; break;
                case TypeCode.String: dbType = DbType.String; break;
                case TypeCode.DateTime: dbType = DbType.DateTime; break;
                case TypeCode.Single: dbType = DbType.Single; break;
                case TypeCode.Byte: dbType = DbType.Byte; break;
                case TypeCode.Boolean: dbType = DbType.Boolean; break;
                case TypeCode.Object: dbType = DbType.Object; break;
                default: dbType = DbType.String; break;
            }
            return dbType;
        }

        /// <summary>
        /// 得到表列信息
        /// </summary>
        /// <param name="strKey"></param>
        /// <returns></returns>
        private Hashtable GetColumnInfo(string strTblName)
        {
            Hashtable hashColumns = new Hashtable();
            string strSQL = string.Empty;
            try
            {
                strSQL = "SELECT TOP 0 * FROM " + strTblName;

                Database db = new Database();
                DbCommand dbComm = db.GetComamnd();
                dbComm.CommandText = strSQL;

                DbDataAdapter dbAdapter = db.GetAdapter(dbComm);

                dbAdapter.SelectCommand = dbComm;

                DataTable dt = new DataTable();

                dbAdapter.FillSchema(dt, System.Data.SchemaType.Mapped);

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    hashColumns.Add(dt.Columns[i].ColumnName, dt.Columns[i].DataType);
                }
            }
            catch (Exception e)
            {

            }

            return hashColumns;
        }

        /// <summary>
        /// 提交事务，关闭连接
        /// </summary>
        public void CommitTrans()
        {
            ts.CommitTrans();
        }

        /// <summary>
        /// 事务回滚，关闭连接
        /// </summary>
        public void RollBackConnection()
        {
            ts.RollBackConnection();
        }

        /// <summary>
        /// 得到表主键列
        /// </summary>
        /// <param name="strTblName">表名</param>
        /// <returns></returns>
        public string GetKeyColumn(string strTblName)
        {
            string strKey = string.Empty;
            try
            {
                string strSQL = "SELECT * FROM " + strTblName + " WHERE 1=2 ";

                Database db = new Database();
                DbCommand dbComm = db.GetComamnd();
                dbComm.CommandText = strSQL;

                DbDataAdapter dbAdapter = db.GetAdapter(dbComm);

                dbAdapter.SelectCommand = dbComm;

                DataTable dt = new DataTable();

                dbAdapter.FillSchema(dt, System.Data.SchemaType.Mapped);

                for (int i = 0; i < dt.PrimaryKey.Length; i++)
                {
                    strKey = dt.PrimaryKey[i].ColumnName;
                    break;
                }
            }
            catch (Exception e)
            {
                throw new Exception("DataAccess.cs文件里的GetKeyColumn()错误");
            }

            return strKey;
        }

        #endregion
        private string GetSQLConnection()
        {
            return ConfigurationManager.AppSettings["ConnectionString"];
        }


        #region 中间库数据操作
        /// <summary>
        /// 获取中间数据库连接字符串
        /// </summary>
        /// <returns></returns>
        private string GetMidSQLConnection()
        {
            return ConfigurationManager.AppSettings["MidConnectionString"];
        }


        /// <summary>
        /// 使用传入的数据库连接信息查询数据
        /// </summary>
        /// <param name="dbi"></param>
        /// <param name="strSPName"></param>
        /// <param name="dictParameter"></param>
        /// <param name="dcError"></param>
        /// <returns></returns>
        public DataTable MidGetDataTableBySPName(string strSPName, Dictionary<string, object> dictParameter)
        {
            DC_Error dcError = new DC_Error();
            SqlConnection sqlConn = null;
            DataTable dt = new DataTable();
            try
            {
                sqlConn = new SqlConnection(GetMidSQLConnection());

                int Rtn = SQLServer.ExecuteDataTableQueryProcedure("dbo", strSPName, ref dictParameter, sqlConn, sqlConn, ref dt);
                if (Rtn != 0)
                {
                    dcError.ErrorCode = "-1";
                    dcError.ErrorDesc = string.Format("SP {0} fail", strSPName);
                }
            }
            catch (Exception ex)
            {

                dcError.ErrorCode = "-1";
                dcError.ErrorDesc = ex.Message;
            }
            finally
            {
                sqlConn = null;
            }

            return dt;
        }

        public DataSet MidGetDataSetBySPName(string strSPName, Dictionary<string, object> dictParameter)
        {
            DC_Error dcError = new DC_Error();
            SqlConnection sqlConn = null;
            DataSet ds = new DataSet();
            try
            {
                sqlConn = new SqlConnection(GetMidSQLConnection());

                int Rtn = SQLServer.ExecuteDataSetQueryProcedure("dbo", strSPName, ref dictParameter, sqlConn, sqlConn, ref ds);
                if (Rtn != 0)
                {
                    dcError.ErrorCode = "-1";
                    dcError.ErrorDesc = string.Format("SP {0} fail", strSPName);
                }
            }
            catch (Exception ex)
            {
                dcError.ErrorCode = "-1";
                dcError.ErrorDesc = ex.Message;
            }
            finally
            {
                sqlConn = null;
            }

            return ds;
        }

        public DataTable MidGetDataTableBySQLText(string strSQL)
        {
            try
            {
                return SQLServer.ExecuteDataTableQuerySQLText(strSQL, GetMidSQLConnection());
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public bool MidExecuteTransactionSP(string strSPName, Dictionary<string, object> dictParameter, ref DC_Error error, ref int iAffectedRow)
        {
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = sqlConn = new SqlConnection(GetMidSQLConnection());
                iAffectedRow = SQLServer.ExecuteNonQueryProcedure("dbo", strSPName, ref dictParameter, sqlConn, sqlConn);

                if (iAffectedRow == 0)
                    return true;
            }
            catch (Exception ex)
            {
                error.ErrorCode = "-2";
                error.ErrorDesc = ex.Message;
            }
            finally
            {
                sqlConn = null;
                dictParameter = null;
            }

            return false;
        }

        /// <summary>
        /// 无返回参数执行sql语句
        /// </summary>
        /// <param name="strSQL"></param>
        public int MidExecuteQuerySQLText(string strSQL)
        {
            SqlConnection sqlConn = sqlConn = new SqlConnection(GetMidSQLConnection());
            int intCount = -1;
            try
            {
                sqlConn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, sqlConn);
                intCount = cmd.ExecuteNonQuery();
                sqlConn.Close();
                return intCount;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 事务执行sql语句
        /// </summary>
        /// <param name="LsSql">所有需要执行的sql语句</param>
        /// <returns></returns>
        public int MidExecuteSqlTrans(List<string> LsSql)
        {
            int intCount = -1;
            //建立连接并打开  
            SqlConnection myConn = new SqlConnection(GetMidSQLConnection());
            myConn.Open();
            SqlCommand myComm = new SqlCommand();
            //注意，SqlTransaction类无公开的构造函数  
            SqlTransaction myTran;
            //创建一个事务  
            myTran = myConn.BeginTransaction();
            try
            {
                //从此开始，基于该连接的数据操作都被认为是事务的一部分  
                //下面绑定连接和事务对象  
                myComm.Connection = myConn;
                myComm.Transaction = myTran; //定位到pubs数据库  
                //利用sqlcommand进行数据操作   
                foreach (string strSql in LsSql)
                {
                    myComm.CommandText = strSql;
                    intCount = myComm.ExecuteNonQuery();
                }
                //提交事务  
                myTran.Commit();
                return intCount;
            }
            catch (Exception err)
            {
                new ApplicationException("事务操作出错，系统信息：" + err.Message);
                myTran.Rollback();
                return -1;
            }
            finally
            {
                myConn.Close();
            }


        }
        #endregion
        

    }
}
